package cn.pconline.autoclub.task.data;

import java.io.BufferedReader;
import java.io.FileReader;
import java.io.IOException;
import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.HashMap;
import java.util.List;
import java.util.Map;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;

import cn.pconline.autoclub.task.AppUtils;
import cn.pconline.autoclub.task.DBUtils;
import cn.pconline.autoclub.task.util.IdGeneratorByTable;

import com.alibaba.fastjson.JSON;
import com.alibaba.fastjson.JSONArray;
import com.alibaba.fastjson.JSONObject;

public class SyncTopic {
    private static final Logger log = LoggerFactory.getLogger(SyncTopic.class);
    
    private static IdGeneratorByTable idGenerator = new IdGeneratorByTable();
    //private static SimpleDateFormat sdf = new SimpleDateFormat("yyyy");
    static List<String> dynaColumns = null;
    static List<String> clubDynaColumns = null;
    static List<String> userDynaColumns = null;
    static List<String> dynaImgColumns = null;

    static {
        try {
            dynaColumns = DBUtils.getColumns("ac_dyna_info_2014");
            clubDynaColumns = DBUtils.getColumns("ac_club_dyna");
            userDynaColumns = DBUtils.getColumns("ac_user_dyna");
            dynaImgColumns = DBUtils.getColumns("ac_dyna_image");
        } catch (SQLException e) {
            log.error("get table columns error.", e);
        }
    }
    
    public static void main(String[] args) {
        AppUtils.initLog(SyncTopic.class);
        String fileName = null;
        if(args != null && args.length > 0) {
            fileName = args[0];
        }
        //fileName = "E:\\work\\autoclub\\branches\\autoclub_4.0_beta\\crontab\\task\\conf\\data.txt";
        if(fileName == null) {
            log.error("the file name must not be null, please input");
            return;
        }
        try {
            log.info("start sync topic");
            int count = readTopicData(fileName, new LineProcess() {
                public int process(String line) {
                    try {
                        JSONObject msg = JSON.parseObject(line);//new JSONObject();
                        log.info("topic: {}", msg);
                        return syncTopic(msg);
                    } catch(RuntimeException ex) {
                        ex.printStackTrace();
                    }
                    return 0;
                }
            });
            log.info("add total: {}", count);
        } catch (IOException e) {
            e.printStackTrace();
        }
        //String text = "{\"agent\":0,\"authorId\":34306209,\"containImage\":true,\"createAt\":1419775170000,\"createIp\":\"106.117.119.4\",\"forumId\":18445,\"forumName\":\"逸动论坛\",\"forumUrl\":\"http://localhost.pcauto.com.cn/bbs7/forum-18445.html\",\"image1\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268331_1419860394789_1024x1024.jpg\",\"image2\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268358_1419860409096_1024x1024.jpg\",\"image3\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268385_1419860429888_1024x1024.jpg\",\"images\":[{\"height\":466,\"seq\":1,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268331_1419860394789_1024x1024.jpg\",\"width\":700},{\"height\":466,\"seq\":2,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268358_1419860409096_1024x1024.jpg\",\"width\":700},{\"height\":1243,\"seq\":3,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268385_1419860429888_1024x1024.jpg\",\"width\":700},{\"height\":466,\"seq\":4,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268414_1419860475712_1024x1024.jpg\",\"width\":700},{\"height\":466,\"seq\":5,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268416_1419860487104_1024x1024.jpg\",\"width\":700},{\"height\":466,\"seq\":6,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268418_1419860494650_1024x1024.jpg\",\"width\":700},{\"height\":466,\"seq\":7,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/29/c44/1268420_1419860502002_1024x1024.jpg\",\"width\":700},{\"height\":933,\"seq\":8,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/28/c37/1224399_1419774732559_1024x1024.jpg\",\"width\":700},{\"height\":933,\"seq\":9,\"url\":\"http://img.pcauto.com.cn/images/upload/upc/tx/bbs6/1412/28/c37/1224340_1419774694027_1024x1024.jpg\",\"width\":700}],\"key\":\"autobbs.topic.create6286534\",\"lastPostAt\":1420391199000,\"photoCount\":18,\"pick\":1,\"postMessage\":\"车友小聚，没事了一块出去玩，不错的选择，以后聚会吃饭可以来这气氛不错的环境也很好！\\n[图]\\n\\n对面就是我们要就餐的地方了\\n[图]\\n又一位车友赶来了我们还等了一会大家才陆续到齐了\\n\\n[图]\\n\\n我们在看这小子的车车，两个孩子也跟着研究开了，也不知道是不是看明白了\\n[图]\\n\\n\\n[图]\\n\\n\\n[图]\\n\\n\\n\\n\\n\\n\\n\\n\\n\\n[图]\\n\\n\\n[图]\",\"recommend\":0,\"tid\":6286534,\"title\":\"【逸动联盟】车友小聚分享给大家！\",\"topicAuthorId\":34306209,\"topicAuthorName\":\"爱车小萱\",\"topicAuthorUrl\":\"http://dev4.pcauto.com.cn:8081/34306209\",\"topicCreateAt\":1419775170000,\"topicId\":6286534,\"topicReplyCount\":26,\"topicUrl\":\"http://localhost.pcauto.com.cn/bbs7/topic-6286534.html\",\"type\":\"\",\"version\":1}";
        //JSONObject msg = JSON.parseObject(text);//new JSONObject();
        //syncTopic(msg);
    }
    
    private static int readTopicData(String fileName, LineProcess callback) throws IOException {
        @SuppressWarnings("resource")
        BufferedReader bufferedReader = new BufferedReader(new FileReader(fileName));
        String line = null;
        int count = 0;
        while((line = bufferedReader.readLine()) != null) {
            count += callback.process(line);
        }
        return count;
    }
    
    static interface LineProcess {
        public int process(String line);
    }

    private static int syncTopic(JSONObject msg) {
        Connection conn = DBUtils.getConnection();
        boolean isAutoCommit = false;
        try {
            isAutoCommit = conn.getAutoCommit();
            conn.setAutoCommit(false);
            
            long topicId = getTopicId(msg);
            long dynaId = getDynaIdByTopicId(conn, topicId);
            if(dynaId > 0) {
                log.info("the topic-{} dyna-{} exists", topicId, dynaId);
                return 0;
            }
            Map<String, Object> topicMap = parseDynaInfo(msg);
            boolean isPic = false;
            dynaId = (Long)topicMap.get("dyna_info_id");
            if(msg.get("images") != null) {
                isPic = true;
            }
            
            long userId = getUserId(msg);
            long clubId = 0;
            try {
                clubId = getClubIdByUserId(conn, userId);
            } catch (Exception e) {
                e.printStackTrace();
            }
            topicMap.put("club_id", clubId);
            int dynaCnt = addTopicDyna(conn, topicMap);
            if(dynaCnt > 0) {
                if(clubId > 0) {
                    addClubDyna(conn, topicMap);
                }
                addUserDyna(conn, topicMap);
                if(isPic) {
                    List<Map<String, Object>> images = parseDynaImages(msg, dynaId);
                    addDynaImage(images, dynaId);
                }
                conn.commit();
            }
            return dynaCnt;
        } catch (SQLException e) {
            e.printStackTrace();
            try {
                conn.rollback();
            } catch (SQLException e1) {
                e1.printStackTrace();
            }
        } finally {
            if (conn != null) {
                try {
                    conn.setAutoCommit(isAutoCommit);
                } catch (SQLException e) {
                    e.printStackTrace();
                }
            }
            DBUtils.releaseConn(conn, null, null);
        }
        return 0;
    }
    
    private static int addTopicDyna(Connection conn, Map<String, Object> topic) throws SQLException {
        String dynaSql = getDynaInfoSql("ac_dyna_info_2014");
        
        log.debug("add dyna\n sql: {}, \n data: {}", dynaSql, topic);
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(dynaSql);
            int idx = 1;
            for(String col : dynaColumns) {
                ps.setObject(idx++, topic.get(col));
            }
            int count = ps.executeUpdate();
            log.info("add {} topic to dyna-{}", count, topic.get("dyna_info_id"));
            return count;
        } finally {
            DBUtils.releaseConn(null, ps, null);
        }
    }
    
    private static int addClubDyna(Connection conn, Map<String, Object> topic) throws SQLException {
        //topic.put("is_top", 0);
        //top_date
        long clubId = (Long)topic.get("club_id");
        String dynaSql = getSql("ac_club_dyna_", clubId);
        log.debug("add club dyna\n sql: {}, \n data: {}", dynaSql, topic);
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(dynaSql);
            int idx = 1;
            for(String col : clubDynaColumns) {
                ps.setObject(idx++, topic.get(col));
            }
            int count = ps.executeUpdate();
            log.info("add {} topic to club dyna_{}-{}", count, topic.get("club_id"), topic.get("dyna_info_id"));
            return count;
        } finally {
            DBUtils.releaseConn(null, ps, null);
        }
    }
    
    private static int addUserDyna(Connection conn, Map<String, Object> topic) throws SQLException {
        long userId = (Long)topic.get("create_by");
        topic.put("user_id", userId);
        String dynaSql = getSql("ac_user_dyna_", userId);
        log.debug("add user dyna\n sql: {}, \n data: {}", dynaSql, topic);
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(dynaSql);
            int idx = 1;
            for(String col : userDynaColumns) {
                ps.setObject(idx++, topic.get(col));
            }
            int count = ps.executeUpdate();
            log.info("add {} topic to user dyna_{}-{}", count, topic.get("user_id"), topic.get("dyna_info_id"));
            return count;
        } finally {
            DBUtils.releaseConn(null, ps, null);
        }
    }
    
    /*private static void addCityDyna() {
        
    }*/
    
    private static int[] addDynaImage(List<Map<String, Object>> images, long dynaId) throws SQLException {
        Connection conn = null;
        PreparedStatement ps = null;
        try {
            conn = DBUtils.getConnection();
            String sql = getSql("ac_dyna_image_", dynaId);
            log.debug("image sql: {}", sql);
            ps = conn.prepareStatement(sql);
            for (Map<String, Object> image : images) {
                int idx = 1;
                log.debug("image: {}", image);
                for (String col : dynaImgColumns) {
                    ps.setObject(idx++, image.get(col));
                }
                ps.addBatch();
            }
            int[] cnt = ps.executeBatch();
            log.info("add {} dyna-{} image", cnt.length, dynaId);
            return cnt;
        } finally {
            DBUtils.releaseConn(null, ps, null);
        }
    }
    
    private static String getDynaInfoSql(String table) throws SQLException {
        StringBuilder dynaSqlBuilder = new StringBuilder("INSERT IGNORE INTO ");
        dynaSqlBuilder.append(table);
        //List<String> columns = DBUtils.getColumns(table);
        StringBuilder dynaColumnBuilder = new StringBuilder();
        StringBuilder dynaParamBuilder = new StringBuilder();
        for(String col : dynaColumns) {
            dynaColumnBuilder.append(',').append(col);
            dynaParamBuilder.append(",?");
        }
        dynaSqlBuilder.append(" (").append(dynaColumnBuilder.substring(1));
        dynaSqlBuilder.append(") values (");
        dynaSqlBuilder.append(dynaParamBuilder.substring(1)).append(")");
        log.debug("dyna insert sql: {}", dynaSqlBuilder);
        return dynaSqlBuilder.toString();
    }
    
    private static String getSql(String tablePrefix, long clubId) throws SQLException {
        StringBuilder dynaSqlBuilder = new StringBuilder("INSERT IGNORE INTO ");
        String table = tablePrefix + (clubId % 10);
        dynaSqlBuilder.append(table);
        List<String> columns = DBUtils.getColumns(table);
        StringBuilder dynaColumnBuilder = new StringBuilder();
        StringBuilder dynaParamBuilder = new StringBuilder();
        for(String col : columns) {
            dynaColumnBuilder.append(',').append(col);
            dynaParamBuilder.append(",?");
        }
        dynaSqlBuilder.append(" (").append(dynaColumnBuilder.substring(1));
        dynaSqlBuilder.append(") values (");
        dynaSqlBuilder.append(dynaParamBuilder.substring(1)).append(")");
        log.debug("club dyna insert sql: {}", dynaSqlBuilder);
        return dynaSqlBuilder.toString();
    }


    protected static long getUserId(JSONObject msg) {
        return msg.getLongValue("topicAuthorId");
    }
    
    private static long getClubIdByUserId(Connection conn, long userId) {
        String sql = "SELECT club_id FROM ac_member_info WHERE user_id = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setLong(1, userId);
            ResultSet rs = ps.executeQuery();
            if(rs != null && rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            log.error("get user-{}'s club id error.", userId);
        }
        return 0;
    }
    
    private static long getDynaIdByTopicId(Connection conn, long topicId) {
        String sql = "SELECT dyna_info_id FROM ac_dyna_info_2014 WHERE topic_id = ?";
        PreparedStatement ps = null;
        try {
            ps = conn.prepareStatement(sql);
            ps.setLong(1, topicId);
            ResultSet rs = ps.executeQuery();
            if(rs != null && rs.next()) {
                return rs.getLong(1);
            }
        } catch (SQLException e) {
            log.error("get dynaId by topicId-{} error.", topicId);
        }
        return 0;
    }
    
    /**
     * @param json转topic,只有创建帖子的时候有效
     * @param clubId
     * @return
     */
    private static Map<String, Object> parseDynaInfo(JSONObject msg) {
        //topicDyna.setType(msg.getString("type"));//帖子类型，question为提问帖标记
        Map<String, Object> dyna = new HashMap<String, Object>();
        dyna.put("dyna_info_id", idGenerator.generate("ac_dyna_info", "dyna_info_id"));
        dyna.put("type", 1);
        long userId = getUserId(msg);
        dyna.put("club_id", 0);
        dyna.put("title", msg.getString("title"));
        dyna.put("content", msg.getString("postMessage"));
        dyna.put("last_post_by", userId);
        dyna.put("last_post_name", msg.getString("topicAuthorName"));
        dyna.put("last_post_at", new Date(msg.getLongValue("lastPostAt")));
        dyna.put("post_count", msg.getIntValue("topicReplyCount"));
        dyna.put("petrol", 0);
        dyna.put("topic_id", getTopicId(msg));
        dyna.put("pick", msg.getIntValue("pick") != 0 ? 1 : 0);
        dyna.put("recommend", msg.getIntValue("recommend") != 0 ? 1 : 0);
        dyna.put("forum_id", msg.getLongValue("forumId"));
        dyna.put("forum_name", msg.getString("forumName"));
        dyna.put("create_date", new Date(msg.getLongValue("topicCreateAt")));
        dyna.put("create_by", userId);
        dyna.put("create_name", msg.getString("topicAuthorName"));
        dyna.put("status", 1);
        dyna.put("is_pic", msg.get("images") != null);
        dyna.put("censored", 1);
        dyna.put("update_by", userId);
        dyna.put("create_at", new Date());
        dyna.put("update_at", new Date());
        dyna.put("province_id", 0);
        dyna.put("city_id", 0);
        dyna.put("praise_count", 0);
        dyna.put("target_id", getTopicId(msg));
        return dyna;
    }
    
    private static long getTopicId(JSONObject msg) {
        return msg.getLongValue("topicId");
    }
    
    /**
     * 帖子图片列表
     * @param msg
     * @param userId
     * @return
     */
    private static List<Map<String, Object>> parseDynaImages(JSONObject msg, long dynaId) {
        JSONArray jsonArray = msg.getJSONArray("images");
        if(jsonArray != null) {
            List<Map<String, Object>> list = new ArrayList<Map<String, Object>>();
            for (int i = 0; i < jsonArray.size(); i++) {
                JSONObject json = JSONObject.parseObject(jsonArray.getString(i));
                Map<String, Object> image = new HashMap<String, Object>();
                String tableName = "ac_dyna_image";
                String url = json.getString("url");
                if (url == null || "".equals(url)) {
                    continue;
                }
                image.put("image_id", idGenerator.generate(tableName, "image_id"));
                image.put("dyna_info_id", dynaId);
                image.put("url", json.getString("url"));
                image.put("width", json.getIntValue("width"));
                image.put("height", json.getIntValue("height"));
                image.put("seq", json.getIntValue("seq"));
                list.add(image);
            }
            return list;
        }
        return null;
    }
}
